package Common.DataAccess.Sql;

import java.util.*;
import Common.*;

/***********************************************************************************
* * ��    ��: SqlAccess
* * �����ռ䣺DataAccess.SqlAccess
* * ����������Sql���ݿ������
* **********************************************************************************/


/** 
 SqlAccess ��Ҫ��װ����Sql���ݿ��һЩ��Ҫ����
 �����������������Լ�һЩ�򵥵����ݿ������
 ֻ��Ϊ���౻������̳У�����ֱ�ӵ���
*/
public abstract class SqlBase
{
//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region Attributes

	private String FConnectionString;
	private SqlConnection sqlConn;

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion Attributes

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region Properties
	public final String getConnectionString()
	{
		return FConnectionString;
	}

	protected final SqlConnection getConnString()
	{
		return sqlConn;
	}
//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion Properties

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region �ֲ�����

	private void FreeSqlConn()
	{
		try
		{
			sqlConn.Close();
			sqlConn.Dispose();
		}
		catch (java.lang.Exception e)
		{
		}
	}

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion �ֲ�����

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region ������������

	/** 
	 ���ι��캯��
	 
	 @param strConn ���ݿ������ִ�
	*/
	public SqlBase(String strConn)
	{
		FConnectionString = strConn;
		try
		{
			sqlConn = new SqlConnection(strConn);
		}
		catch (RuntimeException e)
		{
			FreeSqlConn();
			throw new RuntimeException(e.getMessage());
		}
	}


	/** 
	 �����������ͷ���Դ
	*/
	protected void finalize() throws Throwable
	{
		FreeSqlConn();
	}

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion ������������

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#region ��Ա����
	/** 
	 ִ��һ��INSERT , UPDATE, DELETE ��DML���
	 
	 @param strSQLs Ҫִ�е�һ��SQL
	 @return ����������Ӱ������������� �����׳��쳣��Ϣ
	*/
	public final int ExecuteSQL(StringCollection strSQLs)
	{
		if (strSQLs.size() <= 0)
		{
			return 0;
		}

		int aCount = 0;
		SqlTransaction sqlTrans = null;
		SqlCommand sqlCommand = null;
		try
		{
			sqlCommand = sqlConn.CreateCommand();
			sqlConn.Open();
			sqlTrans = sqlConn.BeginTransaction();
			sqlCommand.Transaction = sqlTrans;
			for (String strSQL : strSQLs)
			{
				sqlCommand.CommandText = strSQL;
				aCount += sqlCommand.ExecuteNonQuery();
			}
			sqlTrans.Commit();
			return aCount;
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			sqlTrans.Rollback();
			throw new RuntimeException(e.getMessage());
		}
		finally
		{
			sqlCommand.Dispose();
			sqlTrans.Dispose();
			sqlConn.Close();
		}
	}

	/** 
	 ִ��INSERT , UPDATE, DELETE ��DML���
	 
	 @param strSQL Ҫִ�еĵ���SQL
	 @return ����������Ӱ������������� �����׳��쳣��Ϣ
	*/
	public final int ExecuteSQL(String strSQL)
	{
		SqlCommand sqlCommand = new SqlCommand(strSQL,sqlConn);
		SqlTransaction sqlTrans = null;
		try
		{
			sqlConn.Open();
			sqlTrans = sqlConn.BeginTransaction();
			sqlCommand.Transaction = sqlTrans;

			int result = sqlCommand.ExecuteNonQuery();
			sqlTrans.Commit();
			return result;
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			sqlTrans.Rollback();
			sqlConn.Close();
			throw new RuntimeException(e.getMessage());
		}
		finally
		{
			sqlCommand.Dispose();
			sqlConn.Close();
		}
	}
	/** 
	 ִ��INSERT , UPDATE, DELETE ��DML���
	 
	 @param strSQL Ҫִ�еĵ���SQL
	 @param oraTrans �����Transaction
	 @return ����������Ӱ������������� �����׳��쳣��Ϣ
	*/
	public final int ExecuteSQL(String strSQL, SqlTransaction sqlTrans)
	{
		try
		{
		SqlCommand sqlCommand = sqlTrans.Connection.CreateCommand();

			sqlCommand.Transaction = sqlTrans;
			sqlCommand.CommandText = strSQL;
			sqlCommand.CommandType = CommandType.Text;

			int result = sqlCommand.ExecuteNonQuery();
			return result;
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			sqlTrans.Rollback();
			sqlConn.Close();
			throw new RuntimeException(e.getMessage());
		}
		finally
		{
			sqlConn.Dispose();
		}
	}
	/** 
	 ִ��һ��INSERT , UPDATE, DELETE ��DML���
	 
	 @param strSQLs Ҫִ�е�һ��SQL
	 @return ����������Ӱ������������� �����׳��쳣��Ϣ
	*/
	public final int ExecuteSQL(String[] strSQLs)
	{
		//modified by den .2004-12-21.
		if (strSQLs.length <= 0)
		{
			return 0;
		}

		int aCount = 0;
		SqlTransaction sqlTrans = null;
		SqlCommand sqlCommand = null;
		try
		{
			sqlCommand = sqlConn.CreateCommand();
			sqlConn.Open();
			sqlTrans = sqlConn.BeginTransaction();
			sqlCommand.Transaction = sqlTrans;
			for (String strSQL : strSQLs)
			{
				sqlCommand.CommandText = strSQL;
				aCount += sqlCommand.ExecuteNonQuery();
			}
			sqlTrans.Commit();
			return aCount;
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			sqlTrans.Rollback();
			throw new RuntimeException(e.getMessage());

		}
		finally
		{
			sqlCommand.Dispose();
			sqlTrans.Dispose();
			sqlConn.Close();
		}
	}

	/** 
	 ִ��һ��INSERT , UPDATE, DELETE ��DML���
	 
	 @param strSQLs Ҫִ�е�һ��SQL
	 @return ����������Ӱ������������� �����׳��쳣��Ϣ
	*/
	public final int ExecuteSQL(ArrayList strSQLs)
	{
		//modified by den .2004-12-21.
		if (strSQLs.size() <= 0)
		{
			return 0;
		}

		int aCount = 0;
		String strSQL;
		SqlTransaction sqlTrans = null;
		SqlCommand sqlCommand = null;
		try
		{
			//				strSQLs.ToString().
			sqlCommand = sqlConn.CreateCommand();
			sqlConn.Open();
			sqlTrans = sqlConn.BeginTransaction();
			sqlCommand.Transaction = sqlTrans;
			Iterator myEnumerator = strSQLs.iterator();

			while (myEnumerator.hasNext())
			{
				strSQL = String.valueOf(myEnumerator.next());
				sqlCommand.CommandText = strSQL;
				aCount += sqlCommand.ExecuteNonQuery();
			}
			sqlTrans.Commit();
			return aCount;
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			sqlTrans.Rollback();
			throw new RuntimeException(e.getMessage());
		}
		finally
		{
			sqlCommand.Dispose();
			sqlTrans.Dispose();
			sqlConn.Close();
		}
	}


	/** 
	 ����һ��SELECT ��䣬�����õ�һ��DataSet
	 
	 @param strSql Ҫ��ѯ��SQL���
	 @return ��������ݣ��򷵻ظò�ѯ�����ݼ�
	*/
	public final DataSet GetDataSet(String strSQL)
	{
		DataSet dataSet = new DataSet("daDataSet");
		SqlDataAdapter sqlAdapter = new SqlDataAdapter(strSQL,sqlConn);
		try
		{
			sqlConn.Open();
			sqlAdapter.Fill(dataSet);
			//if (dataSet.Tables[0].Rows.Count <= 0) 
			//	return null;
			//else
				return dataSet;
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			throw new RuntimeException(e.getMessage());
		}
		finally
		{
			dataSet.Dispose();
			sqlAdapter.Dispose();
			sqlConn.Close();
		}
	}
	/** 
	 ����һ��SELECT ��䣬�����õ�һ��DataSet
	 
	 @param strSql Ҫ��ѯ��SQL���
	 @return ��������ݣ��򷵻ظò�ѯ�����ݼ�
	*/
	public final DataTable GetDataTable(String strSQL)
	{
		DataTable dataTable = new DataTable("daDataTable");

		SqlDataAdapter sqlAdapter = new SqlDataAdapter(strSQL,sqlConn);
		try
		{
			sqlConn.Open();
			sqlAdapter.Fill(dataTable);
			//if (dataTable.Rows.Count <= 0) 
			//	return null;
			//else
			return dataTable;
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			throw new RuntimeException(e.getMessage());
		}
		finally
		{
			dataTable.Dispose();
			sqlAdapter.Dispose();
			sqlConn.Close();
		}
	}
	/** 
	 ����һ��SELECT ����һ��Transaction�������õ�һ��DataTable
	 
	 @param strSql Ҫ��ѯ��SQL���
	 @return ��������ݣ��򷵻ظò�ѯ�����ݼ�
	*/
	public final DataTable GetDataTable(String strSQL, SqlTransaction sqlTrans)
	{
		DataTable dataTable = new DataTable("daDataTable");

		SqlCommand sqlCommand = sqlTrans.Connection.CreateCommand();
		sqlCommand.Transaction = sqlTrans;
		sqlCommand.CommandText = strSQL;
		sqlCommand.CommandType = CommandType.Text;
		SqlDataAdapter sqlAdapter = new SqlDataAdapter(sqlCommand);
		try
		{
			sqlAdapter.Fill(dataTable);
			//if (dataTable.Rows.Count <= 0) 
			//	return null;
			//else
			return dataTable;
		}
//			catch(System.Data.OracleClient.OracleException e)
//			{
//				throw new System.Exception(e.Message);
//			}
		finally
		{
			dataTable.Dispose();
			sqlAdapter.Dispose();
		}
	}


	/** 
	 ����һ��SELECT ��䣬�����õ�Ҫ��ѯ�����ͽ��ֵ
	 
	 @param strSQL
	 @return 
	*/
	public final int GetIntValue(String strSQL)
	{
		SqlCommand sqlComm = new SqlCommand(strSQL,sqlConn);
		try
		{
			sqlConn.Open();
			Object r = sqlComm.ExecuteScalar();
			if (r.equals(null))
			{
				return 0;
			}
			else
			{
				return Integer.parseInt(r.toString());
			}
		}
		finally
		{
			sqlComm.Dispose();
			sqlConn.Close();
		}
	}
	/** 
	 ����һ��SELECT ��䣬�����õ�Ҫ��ѯ�����ͽ��ֵ
	 
	 @param strSQL
	 @return 
	*/
	public final int GetIntValue(String strSQL, SqlTransaction sqlTrans)
	{
		SqlCommand sqlCommand = sqlTrans.Connection.CreateCommand();
		sqlCommand.Transaction = sqlTrans;
		sqlCommand.CommandText = strSQL;
		Object r = sqlCommand.ExecuteScalar();
		if (r.equals(null))
		{
			return 0;
		}
		else
		{
			return Integer.parseInt(r.toString());
		}
	}

	/** 
	 ȡ��sql��䷵�ص�ֵ
	 
	 @param strSQL
	 @return 
	*/
	public final String GetStrValue(String strSQL)
	{
		SqlCommand sqlComm = new SqlCommand(strSQL,sqlConn);
		try
		{
			sqlConn.Open();
			Object r = sqlComm.ExecuteScalar();
			if (r.equals(null))
			{
				return "0";
			}
			else
			{
				return r.toString();
			}
		}
		catch (java.lang.Exception e) //(System.Data.OracleClient.OracleException e)
		{
			return "0";
			//throw new Exception(e.Message);
		}
		finally
		{
			sqlComm.Dispose();
			sqlConn.Close();
		}
	}

	/** 
	 ����һ��SELECT ��䣬�����õ�Ҫ��ѯ��˫���ͽ��ֵ
	 
	 @param strSQL
	 @return 
	*/
	public final double GetDoubleValue(String strSQL)
	{
		SqlCommand sqlComm = new SqlCommand(strSQL,sqlConn);
		try
		{
			sqlConn.Open();
			Object r = sqlComm.ExecuteScalar();
			if (r.equals(null))
			{
				return 0;
			}
			else
			{
				return Double.parseDouble(r.toString());
			}
		}
		catch (java.lang.Exception e) //(System.Data.OracleClient.OracleException e)
		{
			return 0;
			//throw new Exception(e.Message);
		}
		finally
		{
			sqlComm.Dispose();
			sqlConn.Close();
		}
	}

	/** 
	 ����һ��SELECT ��䣬�����õ�Ҫ��ѯ��С�����ֵ
	 
	 @param strSQL
	 @return 
	*/
	public final java.math.BigDecimal GetDecimalValue(String strSQL)
	{
		SqlCommand sqlComm = new SqlCommand(strSQL,sqlConn);
		try
		{
			sqlConn.Open();
			Object r = sqlComm.ExecuteScalar();
			if (r.equals(null))
			{
				return 0;
			}
			else
			{
				return java.math.BigDecimal.Parse(r.toString());
			}
		}
		catch (java.lang.Exception e) //(System.Data.OracleClient.OracleException e)
		{
			return 0;
			//throw new Exception(e.Message);
		}
		finally
		{
			sqlComm.Dispose();
			sqlConn.Close();
		}
	}


	//		/// <summary>
	//		/// ���ô洢����
	//		/// </summary>
	//		/// <param name="strSql"></param>
	//		/// <returns>��������ݣ��򷵻ظò�ѯ�����ݼ�</returns>
	//		protected string SetMonthStat( string procedure_name , string year , string month )
	//		{
	//			OracleCommand oraComm = new OracleCommand( procedure_name , oraConn );
	//			try
	//			{
	//				oraConn.Open();
	//
	//				oraComm.CommandType = CommandType.StoredProcedure;
	//				oraComm.Parameters.Add( "node_id" , OracleType.Number , 10 ).Value = year;
	//				oraComm.Parameters.Add( "base_Cent" , OracleType.Number , 10 ).Value = month;
	//				oraComm.Parameters.Add("i_reason", OracleType.VarChar, 50).Value = "";
	//				oraComm.Parameters.Add("i_remarks", OracleType.VarChar, 100).Value = "";
	//				oraComm.Parameters.Add("return_code", OracleType.Number, 10).Value = "";
	//				oraComm.ExecuteNonQuery();
	//				return "�����ɹ���";
	//			}
	//			catch ( System.Data.OracleClient.OracleException e )
	//			{
	//				throw new Exception (e.Message);
	//			}
	//			finally
	//			{
	//				oraComm.Dispose();
	//				oraConn.Close();
	//			}
	//		}	

	/** 
	 ����SELECT ��䣬 ���ڲ��������Ƿ����
	 
	 @param strSQL ����SQL���
	 @return �������򷵻�true����false
	*/
	public final boolean IsDataExist(String strSQL)
	{
		SqlCommand sqlCommand = new SqlCommand(strSQL,sqlConn);
		try
		{
			sqlConn.Open();
			//���ExecuteOracleScalar����null ˵���ò�ѯ���������Է�������false
			return (!sqlCommand.ExecuteScalar().equals(null));
		}
		catch (System.Data.SqlClient.SqlException e)
		{
			sqlConn.Close();
			throw new RuntimeException(e.getMessage());
		}
		finally
		{
			sqlCommand.Dispose();
			sqlConn.Close();
		}
	}
	public final SqlTransaction BeginTransaction()
	{
		sqlConn.Open();
		return sqlConn.BeginTransaction();
	}

//C# TO JAVA CONVERTER TODO TASK: There is no preprocessor in Java:
		///#endregion ��Ա����
}